<?php

require_once('Vega/Dao/Table.php');

class Mobitv_Admin_UserDao extends Vega_Dao_Table {

    public function search($term, $page, $limit) {
        $offset = $limit * ($page - 1);
        $phone_number = mysql_escape_string($term);

        //$sql = "SELECT * FROM user WHERE id LIKE '%$phone_number%' LIMIT $offset,$limit";
        $sql = "SELECT * FROM user WHERE id = '$phone_number'";

        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        return $rows;
    }

    public function getList($limit, $page) {
        $offset = $limit * ($page - 1);
        $_limit = ' LIMIT ' . $limit;
        $_offset = ' OFFSET ' . $offset;

        $sql = "SELECT * FROM user $_limit $_offset";

        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function countAll() {
        $sql = "SELECT COUNT(id) as numrow FROM user";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        return $row['numrow'];
    }

    public function isExist($mobile) {
        $mobile = "'" . addslashes($mobile) . "'";
        $sql = "SELECT id FROM user WHERE id=$mobile";
        $this->_db->query($sql);
        return $this->_db->fetchRow();
    }

    public function subscribe($userId, $packageId, $expiredTime=null) {
        //$expiredTime = $expiredTime ? $this->_db->valueQuote($expiredTime) : $this->_db->valueQuote(date("Y-m-t 23:59:59"));

        $expiredTime = $expiredTime ? $this->_db->valueQuote($expiredTime) : $this->_db->valueQuote($this->thisDayNextMonth(date("Y-m-d 00:00:00")));
        $userId = $this->_db->valueQuote($userId);
        return $this->_db->query("UPDATE user SET is_subscribed=$packageId, expired_time=$expiredTime WHERE id=$userId");
    }

    public function subscribeWorldcupPackage($userId) {
        $userId = $this->_db->valueQuote($userId);
        return $this->_db->query("INSERT INTO wc_user SET msisdn=$userId, subscribed_time=$this->now ON DUPLICATE KEY UPDATE subscribed_time=$this->now");
    }

    public function isSubscribedWorldcupPackage($userId) {
        $userId = $this->_db->valueQuote($userId);
        $sql = "SELECT msisdn FROM wc_user WHERE msisdn=$userId";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function unsubscribe($userId) {
        $userId = $this->_db->valueQuote($userId);

        // Delete all add-on channel
        $this->_db->query("UPDATE user_channel SET deleted=1 WHERE user_id=$userId");

        return $this->_db->query("UPDATE user SET is_subscribed=0 WHERE id=$userId");
    }

    public function addTime($userId, $hour) {
        $userId = $this->_db->valueQuote($userId);
        $hour = intval($hour * 3600);

        return $this->_db->query("UPDATE user SET quota=quota+$hour WHERE id=$userId");
    }

    public function addAddonChannel($userId, $channelId) {
        $channelId = intval($channelId);
        $userId = $this->_db->valueQuote($userId);
        //$expiredTime = $expiredTime ? $this->_db->valueQuote($expiredTime) : $this->_db->valueQuote(date("Y-m-t 23:59:59"));
        /**
          $this->_db->query("SELECT expired_time, is_subscribed FROM user WHERE id = $userId AND expired_time > now() AND is_debtor = 0");
          $row = $this->_db->fetchRow();
          if($row) {
          $expiredTime = $row['expired_time'];
          $packageId = $row['is_subscribed'];
          $dateCharged = date('Y-m-d 00:00:00', strtotime("-1 months", strtotime($expiredTime)));
          $sql = "SELECT content FROM transaction WHERE msisdn=$userId AND (action='monfee' OR action='subscribe' OR action='switch') AND created_time >= '$dateCharged' AND content = $packageId ORDER BY created_time DESC LIMIT 0, 1";
          $this->_db->query($sql);
          $row = $this->_db->fetchRow();

          $expiredTime = ($row) ? $this->_db->valueQuote($expiredTime) : $this->_db->valueQuote($this->thisDayNextMonth(date("Y-m-d 00:00:00")));
          }
          else $expiredTime = $this->_db->valueQuote($this->thisDayNextMonth(date("Y-m-d 00:00:00")));
          /* */
        $expiredTime = $this->_db->valueQuote($this->thisDayNextMonth(date("Y-m-d 00:00:00")));

        return $this->_db->query("INSERT INTO user_channel SET user_id=$userId, channel_id=$channelId, expired_time=$expiredTime, deleted=0 ON DUPLICATE KEY UPDATE expired_time=$expiredTime, deleted=0");
    }

    public function removeAddonChannel($userId, $channelId) {
        $channelId = intval($channelId);
        $userId = $this->_db->valueQuote($userId);

        return $this->_db->query("INSERT INTO user_channel SET user_id=$userId, channel_id=$channelId, deleted=1 ON DUPLICATE KEY UPDATE deleted=1");
    }

    public function insert($data) {
        $id = "'" . addslashes($data['id']) . "'";
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);
        $password = $this->_db->valueQuote(md5($data['password']));
        $status = intval($data['status']);
        $sql = "INSERT INTO user SET id=$id, fullname=$fullname, email=$email, password=$password, status=$status";
        return $this->_db->queryWrite($sql);
    }

    public function getDetail($id) {
        $id = "'" . addslashes($id) . "'";
        $sql = "SELECT * FROM user WHERE id=$id";
        $this->_db->query($sql);
        return $this->_db->fetchRow();
    }

    public function save($id, $data) {
        $id = "'" . addslashes($id) . "'";
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);
        $status = intval($data['status']);
        if (!empty($data['password'])) {
            $password = $this->_db->valueQuote(md5($data['password']));
            $sql = "UPDATE user
                    SET fullname=$fullname, email=$email, password=$password, status=$status
                    WHERE id=$id";
        } else {
            // Giu password
            $sql = "UPDATE user
                    SET fullname=$fullname, email=$email, status=$status
                    WHERE id=$id";
        }
        return $this->_db->queryWrite($sql);
    }

    public function deleteFromList($list) {
        $list = "(" . implode(",", $list) . ")";
        $sql = "DELETE FROM user WHERE id IN $list";
        return $this->_db->queryWrite($sql);
    }

    public function getLog($userId, $from, $to, $limit = 1000, $page = 1) {
        $offset = $limit * ($page - 1);
        $_limit = ' LIMIT ' . $limit;
        $_offset = ' OFFSET ' . $offset;
        $userId = $this->_db->valueQuote($userId);
        $from = $this->_db->valueQuote($from);
        $to = $this->_db->valueQuote($to);

        $sql = "SELECT msisdn, action, content, charge, '' AS extra, method, created_time, 0 AS 'status' FROM transaction WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, action, content, charge, '', method, created_time, status FROM transaction_error WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'access', hlr_status, 0, model, method, created_time, 0 AS status FROM session WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'access', hlr_status, 0, model, method, created_time, 0 AS status FROM session_storage WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'tv', channel, play_time, model, '', started_time AS created_time, status FROM livetv_watching_list WHERE msisdn=$userId AND started_time>=$from AND started_time<$to
                ORDER BY created_time DESC $_limit $_offset ";
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getCountUserLog($userId, $from, $to) {
        $userId = $this->_db->valueQuote($userId);
        $from = $this->_db->valueQuote($from);
        $to = $this->_db->valueQuote($to);

        $sql = "SELECT msisdn, action, content, charge, '' AS extra, method, created_time, 0 AS 'status' FROM transaction WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, action, content, charge, '', method, created_time, status FROM transaction_error WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'access', hlr_status, 0, model, method, created_time, 0 AS status FROM session WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'access', hlr_status, 0, model, method, created_time, 0 AS status FROM session_storage WHERE msisdn=$userId AND created_time>=$from AND created_time<$to
                UNION
                SELECT msisdn, 'tv', channel, play_time, model, '', started_time AS created_time, status FROM livetv_watching_list WHERE msisdn=$userId AND started_time>=$from AND started_time<$to
                ORDER BY created_time DESC";

        $this->_db->query($sql);
        $return = $this->_db->fetchRowset();
        return sizeof($return);
    }

    public function getAddonChannelList($userId) {
        $userId = $this->_db->valueQuote($userId);

        $sql = "SELECT c.id, c.name FROM user_channel uc INNER JOIN channel c ON uc.channel_id=c.id WHERE uc.user_id=$userId AND uc.deleted=0";

        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function logTransaction($requestId, $userId, $action, $content, $charge, $method) {
        $requestId = $this->_db->valueQuote($requestId);
        $userId = $this->_db->valueQuote($userId);
        $action = $this->_db->valueQuote($action);
        $content = $this->_db->valueQuote($content);
        $method = $this->_db->valueQuote($method);
        $charge = intval($charge);

        $sql = "INSERT INTO transaction SET request_id=$requestId, msisdn=$userId, action=$action, content=$content, charge=$charge, method=$method, created_time=now()";

        return $this->_db->queryWrite($sql);
    }

    public function logTransactionError($requestId, $msisdn, $action, $content="", $charge=0, $method="ws", $status=0) {
        $requestId = $this->_db->valueQuote($requestId);
        $msisdn = $this->_db->valueQuote($msisdn);
        $action = $this->_db->valueQuote($action);
        $content = $this->_db->valueQuote($content);
        $method = $this->_db->valueQuote($method);
        $charge = intval($charge);
        $status = intval($status);

        $sql = "INSERT INTO transaction_error SET request_id=$requestId, msisdn=$msisdn, action=$action, content=$content, charge=$charge, method=$method, status=$status, created_time=NOW()";
        return $this->_db->queryWrite($sql);
    }

    public function logAdminTransaction($adminId, $requestId, $msisdn, $action, $content, $info) {
        $requestId = $this->_db->valueQuote($requestId);
        $msisdn = $this->_db->valueQuote($msisdn);
        $action = $this->_db->valueQuote($action);
        $content = $this->_db->valueQuote($content);
        $info = $this->_db->valueQuote($info);

        $adminId = intval($adminId);

        $sql = "INSERT INTO admin_transaction SET admin_id=$adminId, request_id=$requestId, msisdn=$msisdn, action=$action, content=$content, info=$info, created_time=now()";

        return $this->_db->queryWrite($sql);
    }

    /*
     * Them vao danh sach video qua tang
     * @author      hoanm
     * @date        07/07/2010
     *
     * @param       int $videoId
     * @param       string $sender
     * @param       string $receiver
     */

    public function addIntoPresentList($videoId, $sender, $receiver) {
        $config = Vega_App::getInstance()->coreConfig;
        $expiredTimestamp = time() + $config['video']['present_timeout'] * 3600;
        $expiredTime = "'" . date('Y-m-d H:i:s', $expiredTimestamp) . "'";

        $sender = $this->_db->valueQuote($sender);
        $receiver = $this->_db->valueQuote($receiver);
        $videoId = intval($videoId);

        $sql = "INSERT INTO clip_present SET clip_id=$videoId, sender=$sender, receiver=$receiver, created_time=now(), expired_time=$expiredTime";
        $return = $this->_db->queryWrite($sql);

        $this->_db->query("SELECT id FROM clip_present WHERE clip_id=$videoId AND sender=$sender AND receiver=$receiver ORDER BY created_time DESC LIMIT 1");
        $row = $this->_db->fetchRow();

        return $row ? $row['id'] : 0;
    }

    public function setVip($msisdn, $vip) {
        // Expired time is last day of month
        $expiredTime = $expiredTime ? $this->_db->valueQuote($expiredTime) : $this->_db->valueQuote(date("Y-m-t 23:59:59"));
        $msisdn = $this->_db->valueQuote($msisdn);

        $sql = "INSERT INTO user SET id=$msisdn, is_subscribed=1, charged_time=now(), expired_time=$expiredTime, vip=$vip, quota=36000 ON DUPLICATE KEY UPDATE is_subscribed=1, charged_time=now(), vip=$vip";
        $this->_db->queryWrite($sql);

        $sql = "SELECT id from channel WHERE type=" . ADDON_CHANNEL;
        $this->_db->query($sql);
        while ($row = $this->_db->fetchRow()) {
            $channel_id = $row['id'];
            $sql = "INSERT INTO user_channel SET user_id=$msisdn, channel_id=$channel_id, charged_time=now(), expired_time=$expiredTime, deleted=0 ON DUPLICATE KEY UPDATE charged_time=now(), deleted=0";
            $this->_db->queryWrite($sql);
        }
    }

    public function tempFunction($sql, $option = 0) {
        $this->_db->query($sql);
        if ($option)
            $return = $this->_db->fetchRowset();
        else
            $return = $this->_db->fetchRow();
        return $return;
    }

    public function thisDayNextMonth($date) {
        $day = date('d', strtotime($date));
        $month = date('m', strtotime($date)) + 1;
        $month = ($month > 12) ? 1 : $month;
        $month = ($month > 9) ? $month : "0$month";

        $year = date('Y', strtotime($date));
        $year = ($month == 1) ? $year + 1 : $year;
        $time = date('H:i:s', strtotime($date));

        $date = "$year-$month-$day $time";

        $endmonth = date('Y-m-t 23:59:59', strtotime("$year-$month-01 00:00:00"));

        $date = ($date > $endmonth) ? $endmonth : $date;

        return $date;
    }

    /*
     * Cap nhat trang thai cua video dang xem hoac download
     * @author      hoanm
     * @date        20/11/2009
     *
     * @param       string $userId
     * @param       int $videoId
     */

    public function addIntoWatchingList($userId, $videoId, $action, $charge) {
        $config = Vega_App::getInstance()->coreConfig;
        $expiredTime = time() + $config['video']['watching_timeout'];
        $expiredTime = "'" . date('Y-m-d H:i:s', $expiredTime) . "'";
        $action = $this->_db->valueQuote($action);
        $userId = $this->_db->valueQuote($userId);
        $videoId = intval($videoId);
        $charge = intval($charge);
        $sql = "INSERT INTO watching_list SET user_id=$userId, clip_id=$videoId, charge=$charge, action=$action, started_time=now(), expired_time=$expiredTime";
        $this->_db->queryWrite($sql);
    }

}